from fastapi import APIRouter,UploadFile,File 
from app.models.schemas import DataBaseProjectDoingInputRequest,DOIResponse,PaginatedResponse
from app.services.db_service_factory import get_db_service 
from app.models.database import ProjectDoingDB
from typing import Any

router = APIRouter()



@router.get("/project/filters",response_model=DOIResponse[Any])
async def project_filters():
    """
    筛选条件
    """
    try:
        # 获取数据库服务
        db_service = get_db_service()
        
        # 构建过滤条件
        filters = {}
        #需要查询ProjectDoingDB表中的某个字段的去重后的值
        filters["_distinct"] = True
        filters["_fields"] = ["class1","class2","subject","center_name"]
        #查询ProjectDoingDB表中的所有记录
        result = db_service.query_entities_as_dicts(
            entity_class=ProjectDoingDB,
            filters=filters
        )
        return DOIResponse(
            message="",
            status="success",
            data=result
        )
        
    except Exception as e:
        return DOIResponse(
            message=f"获取查询条件失败: {str(e)}",
            status="error",
            data={}
        )



@router.post("/project/doing",response_model=PaginatedResponse[dict])
async def project_doing(
   request:DataBaseProjectDoingInputRequest
):
    """
    项目进行中
    """
    try:
        # 获取数据库服务
        db_service = get_db_service()
        
        # 构建过滤条件
        filters = {}
        
        # 关键词搜索 - 对 project_name 和 remark 字段进行模糊搜索
        if request.keyword:
            filters["_keyword"] = {
                "value": request.keyword,
                "fields": ["project_name", "center_name","class1","class2","subject"]
            }
        
        # 分类界定 - 使用 IN 操作符
        if request.class1:
            filters["class1"] = {"op": "in", "value": request.class1}
        
        # 研究领域 - 使用 IN 操作符
        if request.class2:
            filters["class2"] = {"op": "in", "value": request.class2}
        
        
        # 科室 - 使用 IN 操作符
        if request.subject:
            filters["subject"] = {"op": "in", "value": request.subject}
        
        # 研究中心 - 使用 IN 操作符
        if request.center_name:
            filters["center_name"] = {"op": "in", "value": request.center_name}
        
        # 使用新的分页查询方法
        result = db_service.query_entities_as_dicts_by_page(
            entity_class=ProjectDoingDB,
            filters=filters,
            page=request.page,
            pageSize=request.pageSize,
            orderBy=request.orderBy
        )
        
        return PaginatedResponse(
            message="",
            status="success",
            page=result["page"],
            pageSize=result["pageSize"],
            total=result["total"],
            totalPages=result["totalPages"],
            items=result["items"]
        )
        
    except Exception as e:
        return PaginatedResponse(
            message=f"查询失败: {str(e)}",
            status="error",
            page=request.page,
            pageSize=request.pageSize,
            total=0,
            totalPages=0,
            items=[]
        )
 
 
#文件上传接口
@router.post("/upload/", response_model=DOIResponse[Any])
async def upload_file(
    file: UploadFile = File(..., description="支持 xlsx、xls 格式的Excel文件"), 
):
    """
    上传Excel文件并将内容插入到ProjectDoingDB表中
    
    Excel文件应包含以下列（列名不区分大小写）：
    - project_name: 项目名称
    - class1: 分类界定
    - class2: 研究领域
    - center_name: 研究中心
    - subject: 科室
    """
    try:
        # 检查文件类型
        if not file.filename.lower().endswith(('.xlsx', '.xls')):
            return DOIResponse(
                message="仅支持Excel文件格式（.xlsx, .xls）",
                status="error",
                data=None
            )
        
        # 读取文件内容
        content = await file.read()
        
        # 解析Excel文件
        import pandas as pd
        import io
        
        excel_data = io.BytesIO(content)
        df = pd.read_excel(excel_data)
        
        # 检查必要的列是否存在
        required_columns = ['project_name']
        optional_columns = ['class1', 'class2', 'center_name', 'subject']
        
        # 将列名转换为小写进行匹配
        df_columns_lower = {col.lower(): col for col in df.columns}
        
        # 检查必要列
        missing_columns = []
        for col in required_columns:
            if col.lower() not in df_columns_lower:
                missing_columns.append(col)
        
        if missing_columns:
            return DOIResponse(
                message=f"Excel文件缺少必要的列: {', '.join(missing_columns)}",
                status="error",
                data=None
            )
        
        # 获取数据库服务
        db_service = get_db_service()
        
        # 准备插入的数据
        insert_data = []
        success_count = 0
        error_count = 0
        
        for index, row in df.iterrows():
            try:
                # 构建数据字典
                project_data = {}
                
                # 必要字段
                project_data['project_name'] = str(row[df_columns_lower.get('project_name', 'project_name')]) if pd.notna(row[df_columns_lower.get('project_name', 'project_name')]) else ""
                
                # 可选字段
                for col in optional_columns:
                    if col.lower() in df_columns_lower:
                        original_col = df_columns_lower[col.lower()]
                        project_data[col] = str(row[original_col]) if pd.notna(row[original_col]) else ""
                    else:
                        project_data[col] = ""
                
                # 跳过空的项目名称
                if not project_data['project_name'].strip():
                    continue
                
                insert_data.append(project_data)
                success_count += 1
                
            except Exception as e:
                error_count += 1
                print(f"处理第{index+1}行数据时出错: {str(e)}")
                continue
        
        # 批量插入数据库
        if insert_data:
            for data in insert_data:
                db_service.save_entity(ProjectDoingDB, data)
        
        return DOIResponse(
            message=f"文件上传成功！共处理 {len(df)} 行数据，成功插入 {success_count} 条记录，失败 {error_count} 条",
            status="success",
            data={
                "total_rows": len(df),
                "success_count": success_count,
                "error_count": error_count,
                "filename": file.filename
            }
        )
        
    except Exception as e:
        return DOIResponse(
            message=f"文件上传失败: {str(e)}",
            status="error",
            data=None
        )
 
